﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace HDSmartCommunity.DAL
{
    public class CommFN<T>
    {
        /*public bool ExecFN(string sql)
        {
            SQLiteCommand command = new SQLiteCommand();
            using (SQLiteConnection connection = DataFactory.GetSQLConnection())
            {
                PrepareCommand(command, connection, sql);
                return command.ExecuteNonQuery() > 0;
            }
        }*/

        public bool ExecFN(string sql)
        {
            SqliteCommand command = new SqliteCommand();
            command.CommandText = sql;
            using (SqliteConnection connection = DataFactory.GetSqliteConnection())
            {
                PrepareCommand(command, connection, sql);
                return command.ExecuteNonQuery() > 0;
            }
        }

        public object ExecuteScalar(string sql)
        {
            SqliteCommand command = new SqliteCommand();
            command.CommandText = sql;
            using (SqliteConnection connection = DataFactory.GetSqliteConnection())
            {
                PrepareCommand(command, connection, sql);
                return command.ExecuteScalar();
            }
        }

        private static void PrepareCommand(SqliteCommand cmd, SqliteConnection conn, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
        }

        /*private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
        }*/

       /* public List<T> GetDataList(string sql)
        {
            DataTable TSelectReSoult = new DataTable();
            using (var connection = DataFactory.GetSQLConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SQLiteCommand dbCommand = connection.CreateCommand();
                dbCommand.CommandText = sql;
                SQLiteDataReader reader = dbCommand.ExecuteReader();
                TSelectReSoult.Load(reader);
                return ConvertToList<T>(TSelectReSoult);
            }
        }*/

        public List<T> GetDataList(string sql)
        {
            DataTable TSelectReSoult = new DataTable();
            using (var connection = DataFactory.GetSqliteConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqliteCommand dbCommand = connection.CreateCommand();
                dbCommand.CommandText = sql;
                SqliteDataReader reader = dbCommand.ExecuteReader();
                TSelectReSoult.Load(reader);
                return ConvertToList<T>(TSelectReSoult);
            }
        }

        public List<T> ConvertToList<T>(DataTable dt)
        {
            var columnNames = dt.Columns.Cast<DataColumn>()
                    .Select(c => c.ColumnName)
                    .ToList();
            var properties = typeof(T).GetProperties();
            return dt.AsEnumerable().Select(row =>
            {
                var objT = Activator.CreateInstance<T>();
                foreach (var pro in properties)
                {
                    if (columnNames.Contains(pro.Name))
                    {
                        PropertyInfo pI = objT.GetType().GetProperty(pro.Name);
                        pro.SetValue(objT, row[pro.Name] == DBNull.Value ? null : Convert.ChangeType(row[pro.Name], pI.PropertyType));
                    }
                }
                return objT;
            }).ToList();
        }

        /*public T GetData(string sql)
        {
            List<T> list = new List<T>();
            list = GetDataList(sql);
            return list.FirstOrDefault<T>();
        }
*/
        public T GetData(string sql)
        {
            List<T> list = new List<T>();
            list = GetDataList(sql);
            return list.FirstOrDefault<T>();
        }
    }
}
